using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
namespace GeneralClasses
{

    ///larger container class that holds all of the data for a specific day

    #region NOTES
    /*
13 OCT 2010 - Created class, cant put it in so nothing is tested, still writing in notepadd++.
*/
    #endregion


    public class Day
    {

        #region VARIABLES
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        private int dayID;
        private DateTime dateTime; // instance of the date class, not actual numerical date
        private List<Person> deprecated_persons; // not sure if we need this
        private List<Shift> deprecated_shifts; // the numner of shifts per day could be different
        private DayOfWeek dayOfWeek;

        private List<Department> departments; // list of all departments that inside this day. This is generally constant day to day.

        private DatabaseConnection dbc;

        public Schedule schedulePointer; // public pointer to the schedule class. 

        #endregion

        #region PROPERTIES
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================

        public Schedule SchedulePointer
        {
            get { return schedulePointer; }
            set { schedulePointer = value; }
        }

        public List<Department> Departments
        {
            get { return departments; }
            set { departments = value; }
        }

        public DayOfWeek DayOfWeek
        {
            get { return dayOfWeek; }
            set { dayOfWeek = value; }
        }

        /* public String ConnectionString
         {
             get
             {
                 return dbc.ConnectionString;
             }
             set
             {
                 dbc.ConnectionString = value;
             }
         }*/

        public int DID
        {
            get { return dayID; }
            set { dayID = value; }
        }

        public List<Shift> deprecated_Shifts
        {
            get { return deprecated_shifts; }
            set { deprecated_shifts = value; }
        }

        public List<Person> deprecated_Persons
        {
            get { return deprecated_persons; }
            set { deprecated_persons = value; }
        }

        public DateTime DateTime
        {
            get { return dateTime; }
            set
            {
                dateTime = new DateTime(value.Year, value.Month, value.Day);
            }
        }

        #endregion

        #region CONSTRUCTOR
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================


        public Day()
        {
            //default blank values
            dayID = -1;
            dateTime = new DateTime();
            deprecated_persons = new List<Person>();
            deprecated_shifts = new List<Shift>();

            dayOfWeek = new DayOfWeek();
            dbc = new DatabaseConnection();
            departments = new List<Department>();
            //this.schedulePointer = containerSchedule;

        }

        #endregion

        #region PRIVATE

        //==============================================================================
        //                                                                       PRIVATE
        //==============================================================================


        /// <summary>
        /// returns all shifts in all departments (in this day) as one list
        /// </summary>
        public List<Shift> getAllShifts()
        {
            List<Shift> shiftList = new List<Shift>();
            foreach (Department department in departments)
            {
                shiftList.AddRange(department.Shifts);
            }


            //remove all that don't belong on this day
            for (int i = 0; i <= shiftList.Count - 1; i++)
            {
                if (shiftList[i].StartTime.Day != this.dateTime.Day)
                {
                    shiftList.RemoveAt(i);
                    i++;
                }
            }

            //remove redundancies (same shift, different pointer)
            for (int i = 0; i <= shiftList.Count - 2; i++)
            {
                for (int q = i + 1; q <= shiftList.Count - 1; q++)
                {
                    if (shiftList[i].Equals(shiftList[q]))
                    {
                        shiftList.RemoveAt(q);
                        q--;
                    }
                }
            }

            return shiftList;
        }

        /// <summary>
        /// delete's the given DateTime out of the database. 
        /// This does not remove the Day's data, just the dateTime
        /// </summary>
        /// <param name="dateID"></param>
        private void deleteDate()
        {
            try
            {
                if (!this.checkDateUsed())
                {
                    dbc.executeNonQuery("DELETE FROM D WHERE did = '" + this.dayID + "'");
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        /// <summary>
        /// puts this Day's departments to the department master list. its just a copy of the pointers. 
        /// </summary>
        private void loadAllDepartments()
        {
            try
            {
                //this.departments = schedulePointer.DepartmentMasterList; // old way

                //load this date's dayof week this is separate from the DayOfWeek we gain out of the datetime. 
                DataTable table = dbc.query("SELECT dowid FROM datehasdayofweekrelationship WHERE did ='" + this.dateTime + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        DayOfWeek dow = new DayOfWeek();
                        dow.getDOWID = (int)row["dowid"];
                        dow.loadData();
                        this.dayOfWeek = dow;
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// loada all the shifts that are relevant to this day.
        /// the SqlDataReader already has a list of the shift IDs
        /// </summary>
        /// <param name="reader"></param>
        private void deprecated_loadShifts(DataTable table)
        {
            if ((table != null) && table.Rows.Count > 0)
            {  // get the shifts in this day
                foreach (DataRow row in table.Rows)
                {
                    Shift shift = new Shift();
                    shift.schedulePointer = this.schedulePointer;
                    shift.ShiftID = (int)row["shid"];
                    //shift.ConnectionString = this.ConnectionString;
                    shift.loadData();
                    deprecated_shifts.Add(shift);
                }
            } // otherwise this day has no shifts
        }

        private int removeDayRelations()
        {
            try
            {
                if (dayID != -1)
                {
                    dbc.executeNonQuery("DELETE FROM personcalloffdaterelationship WHERE did='" + dayID + "'");
                    dbc.executeNonQuery("DELETE FROM personvacationdaterelationship WHERE did='" + dayID + "'");
                    dbc.executeNonQuery("DELETE FROM datehasshiftrelationship WHERE did='" + dayID + "'");
                    dbc.executeNonQuery("DELETE FROM datehasdayofweekrelationship WHERE did='" + dayID + "'");
                    return 0;
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }

        }


        //if the given date is used in any relationship, return true, otherwise false;
        private bool checkDateUsed(DateTime dateTime)
        {
            try
            {
                if (dbc.query("SELECT R.did from personcalloffdaterelationship R where R.did = '" + this.dayID.ToString() + "'").Rows.Count > 0) { return true; }
                else if (dbc.query("SELECT R.did from personvacationdaterelationship R where R.d = '" + dateTime.ToString() + "'").Rows.Count > 0) { return true; }
                else if (dbc.query("SELECT R.did from datehasshiftrelationship R where R.d = '" + dateTime.ToString() + "'").Rows.Count > 0) { return true; }
                else if (dbc.query("SELECT R.did from datehasdayofweekrelationship R where R.d = '" + dateTime.ToString() + "'").Rows.Count > 0) { return true; }
                else return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// if the given dateID is in use, then it will return true, otherwise false
        /// </summary>
        /// <param name="dateID"></param>
        /// <returns></returns>
        private bool checkDateUsed()
        {
            try
            {
                if (dbc.query("SELECT R.did from personcalloffdaterelationship R where R.did = '" + dayID + "'").Rows.Count > 0) { return true; }
                else if (dbc.query("SELECT R.did from personvacationdaterelationship R where R.did = '" + dayID + "'").Rows.Count > 0) { return true; }
                else if (dbc.query("SELECT R.did from datehasshiftrelationship R where R.did = '" + dayID + "'").Rows.Count > 0) { return true; }
                else if (dbc.query("SELECT R.did from datehasdayofweekrelationship R where R.did = '" + dayID + "'").Rows.Count > 0) { return true; }
                else return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private bool deprecated_checkDateDayOnly(DateTime dt)
        {
            //DateTime dayOnly = dt.Day;
            //return checkDate(dayOnly);
            return false;
        }

        ///returns whether or not this date exists in the database
        private static bool checkDateTimeExists(DateTime dateTime)
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                //DataTable table = dbc.query("SELECT d FROM D WHERE d>'" + twoDaysEarlier + "'");
                DataTable table = dbc.query("SELECT d FROM D WHERE d='" + dateTime + "'");
                if ((table != null) && (table.Rows.Count > 0))
                {
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        UTIL.sayDebug("checking index: " + i);
                        String dateTimeString = (String)table.Rows[i]["d"].ToString().Trim();

                        UTIL.sayDebug("testing string: " + dateTime + " _ " + dateTimeString);
                        DateTime compareTime = DateTime.Parse(dateTimeString);
                        if (compareTime.Day == dateTime.Day) { return true; }
                    }
                }
                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private int findDayID()
        {
            try
            {
                int lastrow = -1;
                DataTable table = dbc.query("SELECT did,d FROM D WHERE d='" + this.dateTime + "'");
                if ((table != null) && (table.Rows.Count > 0))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        DateTime compareTime = DateTime.Parse(row["d"].ToString());
                        if (compareTime.Day == dateTime.Day)
                        {
                            return (int)row["did"];
                        }
                        lastrow = (int)row["did"];
                    }
                }

                return lastrow;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #endregion

        #region PUBLIC
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================

        public int writeOutObjectTree(int uniqueCounter, int sepCounter, int spaceIndex)
        {
            String spaces = "";
            Department oldDepartment = departments[0];
            int departmentCounter = 0;

            for (int i = 0; i < spaceIndex; i++) { spaces = spaces + "\t"; }
            UTIL.sayDebug("(" + uniqueCounter + ")" + spaces + "(" + sepCounter + ")" + this.GetType().Name);

            foreach (Department department in departments)
            {
                if (!department.Equals(oldDepartment))
                {
                    uniqueCounter++;
                    departmentCounter++;
                    oldDepartment = department;
                }
                uniqueCounter = department.writeOutObjectTree(uniqueCounter, departmentCounter, spaceIndex + 1, this.dateTime);
            }

            return uniqueCounter;
        }

        public void setWithIndex(Person person, int shiftIndex, int departmentIndex)
        {
            departments[departmentIndex].setWithIndex(person, this.dateTime, shiftIndex);
        }

        public void personalize(Person person)
        {
            foreach (Department d in departments)
            {
                d.personalize(person);
            }
            #region deprecated
            /*//remove any empty ones
            for (int i = 0; i <= departments.Count - 1; i++)
            {
                if (departments[i].Shifts.Count == 0)
                {
                    departments.RemoveAt(i);
                }
            }*/
            #endregion
        }

        /// <summary>
        /// personalizes THIS day instance according to the given person object. 
        /// It will throw out all data that does not pertain to this person instance.
        /// </summary>
        /// <param name="person">Person instance for this to personalize for</param>
        public void deprecated_personalize(Person person)
        {
            /*
            //only keep this given person
            deprecated_persons.Clear();
            deprecated_persons.Add(person);

            //load all the shifts of this person on this day
            DataTable table = dbc.query("select perid from person"); // just passing in a junk query, constructor problems
            deprecated_shifts.Clear();
            //get all the shifts IDs for this person on this day. 
            reader = dbc.query("SELECT R.shid FROM datehasshiftrelationship S, personhasassaignedshiftrelationship R WHERE S.did = '" + this.dayID + "' AND S.shid = R.shid AND R.perid = '" + person.PERID + "'");
            this.deprecated_loadShifts(reader);
            */
        }

        /// <summary>
        /// loads all the data for this instance of this class from the database.
        /// Make sure you set the DID before you load the data, as that is what it is based on
        /// </summary>
        public void loadData()
        {
            try
            {
                DataTable table = dbc.query("SELECT D.d FROM D WHERE D.did = '" + dayID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        this.dateTime = (DateTime)row["d"];
                    }
                }

                //load this date's dayof week this is separate from the DayOfWeek we gain out of the datetime. 
                table = dbc.query("SELECT dowid FROM datehasdayofweekrelationship WHERE did ='" + this.dateTime + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        DayOfWeek dow = new DayOfWeek();
                        dow.getDOWID = (int)row["dowid"];
                        dow.loadData();
                        this.dayOfWeek = dow;
                    }
                }

                #region deprecated
                //load shifts for this day THIS HAS BEEN DEPRECATED AS DEPARTMENT IS UNDERNEATH DATE NOW
                //reader = dbc.query("SELECT shid FROM datehasshiftrelationship S WHERE S.did = '" + dayID + "'");
                //this.deprecated_loadShifts(reader);
                //concat all the persons in the shifts for this day
                /*foreach (Shift s in deprecated_shifts)
                {
                    deprecated_persons.AddRange(s.getAssignedPersons());
                }
                deprecated_persons = UTIL.removeRedundantPersons(deprecated_persons);*/
                #endregion

                //load all departments
                this.loadAllDepartments();
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        /// <summary>
        /// saves this Day's data into the database
        /// </summary>
        public int saveData()
        {
            try
            {
                if (this.dayID == -1)
                {
                    if (!checkDateTimeExists(this.dateTime))
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO D (d) values ('" + dateTime + "')");
                        // dbc.executeNonQuery("INSERT INTO D (d) values ('" + dateTime.ToString() + "')");
                    }

                    this.dayID = findDayID();
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE D SET d='" + dateTime + "' WHERE did='" + this.dayID + "'");
                }

                //------------------------------------
                int dowid = this.dayOfWeek.saveData();

                if (dowid == -1)
                {
                }

                // remove old day of week relationship                
                dbc.executeNonQuery("DELETE FROM datehasdayofweekrelationship WHERE did = '" + this.dayID + "'");

                //day of week relationship
                dbc.executeNonQuery("INSERT INTO datehasdayofweekrelationship (did, dowid) values ('" + this.dayID + "','" + dowid + "')");
                //------------------------------------


                //day has all of the departments which have shifts

                #region deprecated
                /*//------------------------------------
            //foreach shift
            foreach (Shift s in deprecated_shifts)
            {
                if (s.ShiftID != -1)
                {
                    //remove old shift relationship
                    dbc.executeNonQuery("DELETE FROM dayhasshiftrelationship WHERE did = '" + DID + "' AND shid = '" + s.ShiftID + "'");
                }
                //save shift
                int shid = s.saveData();

                //dayhasshiftsrelationship
                dbc.executeNonQuery("INSERT INTO dayhasshiftrelationship (did, shid) values ('" + this.dayID + "','" + shid + ")");
            }
            //------------------------------------*/

                /*//------------------------------------
                //foreach shift
                foreach (Shift s in deprecated_shifts)
                {
                    if (s.ShiftID != -1)
                    {
                        //remove old shift relationship
                        dbc.executeNonQuery("DELETE FROM dayhasshiftrelationship WHERE did = '" + DID + "' AND shid = '" + s.ShiftID + "'");
                    }
                    //save shift
                    int shid = s.saveData();

                    //dayhasshiftsrelationship
                    dbc.executeNonQuery("INSERT INTO dayhasshiftrelationship (did, shid) values ('" + this.dayID + "','" + shid + ")");
                }
                //------------------------------------*/

                /*//------------------------------------
                //foreach person
                foreach (Person p in deprecated_persons)
                {
                    //save person
                    int perid = p.saveData();
                }
                //------------------------------------*/
                #endregion

                return this.dayID;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        /// <summary>
        /// removes this day's data from the database. 
        /// WARNING: THIS CANNOT BE UNDONE.
        /// </summary>
        public int removeData()
        {
            try
            {
                #region deprecated
                //if (dayOfWeek.removeData() == -1) { return -1; }
                /*foreach (Department d in departments)
                {
                    if (d.removeData() == -1) { return -1; }
                }*/
                #endregion

                if (this.dayID != -1)
                {
                    removeDayRelations();
                    if (!checkDateUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM D WHERE did='" + this.dayID + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// if this given DateTime is not not in the database, then put it in.
        /// </summary>
        /// <param name="dateTime"></param>
        public void deprecated_addDate(DateTime dateTime)
        {
            if (!this.checkDateUsed(dateTime))
            {

            }
        } // end addDate

        /// <summary>
        /// returns the dateID of this unique dateTime
        /// if there is none, it returns -1
        /// </summary>
        /// <param name="dateTime">datetime to be passed in</param>
        /// <returns>int dateID</returns>
        public int getDateID(DateTime dateTime)
        {
            try
            {
                DateTime twoDaysEarlier = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day);
                twoDaysEarlier = twoDaysEarlier.AddDays(-2);
                DataTable table = dbc.query("SELECT did,d FROM D WHERE d>='" + twoDaysEarlier + "'");
                if ((table != null) && (table.Rows.Count > 0))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        DateTime compareTime = DateTime.Parse(row["d"].ToString());
                        if (compareTime.Day == dateTime.Day)
                        {
                            return (int)row["did"];
                        }
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns the DayOfWeek ID number from the database depending on the given dateTime
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public int getDayOfWeekID(DateTime dateTime)
        {
            try
            {
                DataTable table = dbc.query("SELECT dowid FROM DayOfWeek WHERE name = '" + dateTime.DayOfWeek.ToString() + "'");
                if ((table != null) && table.Rows.Count > 0) { return (int)table.Rows[0]["dowid"]; } else return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public DateTime deprecated_getDate() { return new DateTime(); }

        /// <summary>
        /// returns all the employees that are working on this day. 
        /// NOTE: There is already a list that is constructed for this purpose, making this function obsolete. 
        /// </summary>
        /// <returns></returns>
        public List<String> deprecated_getEmployeeNames()
        {
            /*List<String> workers = new List<String>();
            foreach(Person p in people) workers.add(p.getName());
            return workers;*/
            return null;
        }

        #endregion

    } // end class


} //end namespace